import re
import pymysql
import requests
from bs4 import BeautifulSoup

url = 'https://www.kingofbupt.com/blog/'


def get_data():
    res = requests.get(url)
    soup = BeautifulSoup(res.text, 'lxml')
    text = soup.prettify()
    p = re.findall('<p>\s*(.*?)\s*</p>', text, re.S)
    h1 = re.findall('<h1>\s*(.*?)\s*</h1>', text, re.S)
    write_data(p, h1)
    return text


def write_data(p, h1):
    db = pymysql.connect("localhost", "root", "123456", "test")
    cursor = db.cursor()

    sql = """CREATE TABLE `P` (
             P CHAR(255) null
             )"""

    cursor.execute(sql)

    for i in p:
        sql = "INSERT INTO P(P) VALUES ('%s')" % i
        try:
            cursor.execute(sql)
            db.commit()
        except:
            db.rollback()

    sql = """CREATE TABLE `H1` (
                 H1 CHAR(255) null
                 )"""

    cursor.execute(sql)

    for i in h1:
        sql = "INSERT INTO H1(H1) VALUES ('%s')" % i
        try:
            cursor.execute(sql)
            db.commit()
        except:
            db.rollback()

    db.close()


def write_file(text):
    file = open('text.txt', 'w')
    file.write(text)
    file.close()


data = get_data()
write_file(data)
